package batch.import_user;

import java.sql.*;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbutils.*;
import org.apache.log4j.*;

import batch.common.db.ConnectionManager;

public class changeOptoutToIn_ger
{

    public static void main(String[] args)
    {
        Logger.getLogger("import_user.changeOptoutToIn").info("( * ) Start.");

        changeOptoutToIn_ger obj = new changeOptoutToIn_ger();
        obj.init();

        Logger.getLogger("import_user.changeOptoutToIn").info("( * ) End.");
    }

    public void init() {
    	
    	StringBuffer sbQuery = new StringBuffer();
    	
    	sbQuery.append("SELECT USER_NO, CODE, FLAG \n");
    	sbQuery.append("FROM G_TEMP_USER_NO \n");
    	sbQuery.append("GROUP BY USER_NO, CODE, FLAG \n");


        StringBuffer sQuery2 = new StringBuffer()
                              .append("SELECT COUNT(*)         \n")
                              .append("  FROM G_MB_OPTIN       \n")
                              .append(" WHERE USER_NO = ?      \n")
                              .append("   AND CODE = '0113003' \n");

        Connection conn   = null;
        Statement  stmt   = null;
        ResultSet rs      = null;

        int    i = 0;
        int    j = 0;

        String user_no = "";
        String seq     = "";

        try{
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);
            stmt= conn.createStatement();
            rs = stmt.executeQuery(sbQuery.toString());

            QueryRunner qRunner = new QueryRunner();

            while (rs.next()) {
                i++;

                user_no = ObjectUtils.toString(rs.getObject(1));
                seq     = ObjectUtils.toString(rs.getObject(2));

                ResultSetHandler rsh = new ScalarHandler(1);
                
                int cnt = ((Number)qRunner.query(conn, sQuery2.toString(), user_no, rsh)).intValue();
                if(cnt < 1){
                	
                	sbQuery = new StringBuffer();
                	
                	sbQuery.append("INSERT INTO G_MB_OPTIN( USER_NO        \n")
                           .append("                      , CODE)          \n")
                           .append("                VALUES( ?              \n")
                           .append("                      , '0113003')     \n");

                    qRunner.update(conn, sbQuery.toString(), user_no);
                    
                    sbQuery = new StringBuffer();
                    
                    sbQuery.append("INSERT INTO G_MB_OPTIN_HISTORY( SEQ_ID                          \n")
                           .append("                              , USER_NO                         \n")
                           .append("                              , CODE                            \n")
                           .append("                              , STATUS_CD                       \n")
                           .append("                              , HIS_CD)                         \n")
                           .append("                        VALUES( SEQ_G_MB_OPTIN_HISTORY.NEXTVAL  \n")
                           .append("                              , ?                               \n")
                           .append("                              , '0113003'                       \n")
                           .append("                              , 'Y'                             \n")
                           .append("                              , '1003020')                      \n");

                    qRunner.update(conn, sbQuery.toString(), user_no);
                    
                    sbQuery = new StringBuffer();
                    
                    sbQuery.append("UPDATE G_TEMP_USER_NO  \n");
                    sbQuery.append("   SET FLAG = 'I'        \n");
                    sbQuery.append("where user_no = ? \n");
                    sbQuery.append("    and code = '0113003' \n");
                    
                    qRunner.update(conn, sbQuery.toString(), user_no);
                    
                    j++;
                    
                }

                if(i % 300 == 0){
                    Logger.getLogger("import_user.changeOptoutToIn").info("here we are == " + i);
                }
            }

            Logger.getLogger("import_user.changeOptoutToIn").info("Total of Duplicate User_no Count = " + i);
            Logger.getLogger("import_user.changeOptoutToIn").info("Total of Chagne Count = " + j);

            conn.commit();
        }
        catch(Exception ex){
            if (conn != null) try { conn.rollback();  } catch (Exception ep) {}

            ex.printStackTrace();
            Logger.getLogger("import_user.changeOptoutToIn").info(ex.toString());
        }
        finally{
            if (rs  != null)  try { rs.close();   } catch (Exception se) {}
            if (stmt != null) try { stmt.close(); } catch (Exception se) {}
            if (conn != null) try { conn.close(); } catch (Exception se) {}
        }
    }
}
